<?php
function getResourceFilesByCategory($catID) {
	global $dbname, $db, $db_selected;
	
	$sql = "SELECT 
				F.FID,F.CatID,F.FileTitle,F.FileDesc,F.FileName,F.FileSize,F.FileType,F.DateUpdate,
				C.DirectoryName
			FROM cdc_Files F
			LEFT JOIN cdc_sysCategory C ON F.CatID = C.CatID
			WHERE 
				F.CatID = '$catID' AND
				F.Deleted = 0";
	$result = mysql_db_query($dbname,$sql);	
	
	$stringJSON   = '({"Level":"'.$_SESSION['level'].'","Files":[ ';
	
	while ($value = mysql_fetch_assoc($result)) {
		$value['ConvFileSize'] = convertFileSize($value['FileSize']);
		$value['MimeIconClass']= setMimeIconClass($value['FileType']);
		$catDirArray = getDirFromCategory($catID);
	
		$value['FilePath'] = SITE_URL.'/public_files/resource_files/'.$catDirArray['CatPath'].'/'.$value['FileName'];
	
		$fileDate = new Zend_Date($value['DateUpdate'], Zend_Date::ISO_8601, 'en_US');
		$newFileDate = $fileDate->toString('MMM d');
		$value['FileDateFormat'] = $newFileDate;
		$stringJSON .= Zend_Json::encode($value).',';
	}	
	$stringJSON = substr($stringJSON,0,-1);
	$stringJSON .= ']})';
	return $stringJSON;
}

function getResourceFile($fileID,$render='json') {
	global $dbname, $db, $db_selected;
	
	$sql = "SELECT 
				F.FID,F.CatID,F.FileTitle,F.FileDesc,F.FileName,F.FileSize,F.FileType,F.DateUpdate,
				C.DirectoryName
			FROM cdc_Files F
			LEFT JOIN cdc_sysCategory C ON F.CatID = C.CatID
			WHERE 
				F.FID = '$fileID'";
	$result = mysql_db_query($dbname,$sql);
	$row = mysql_fetch_assoc($result);
	
	/*
	 * Check to see if F.CatID is a main or sub-category
	 */
	$catDirArray = getDirFromCategory($row['CatID']);
	
	$row['FileURL']  = SITE_URL.'/public_files/resource_files/'.$catDirArray['CatPath'].'/'.$row['FileName'];
	$row['FilePath'] = PUBLIC_FILES_PATH.'resource_files/'.$catDirArray['CatPath'].'/'.$row['FileName'];
	$row['MainCatID']= $catDirArray['MainCatID'];
	$row['SubCatID'] = $catDirArray['SubCatID'];
	
	$row['SiteArea'] = $catDirArray['SiteArea'];
	$stringJSON = Zend_Json::encode($row);
	$stringArray = $row;
	
	if ($render == 'json') {
		return $stringJSON;
	} elseif ($render == 'array') {
		return $stringArray;
	}	
}

function getFileCategories($siteArea,$mainCat=null) {
	global $dbname, $db, $db_selected;
	
	$siteArea = urldecode($siteArea);
	
	if ($mainCat>0) {
		$sqlSubCat = ' AND C.ParentID = '.$mainCat.' ';
		$catType = 'SubCat';
		$mainID  = $mainCat;
	} else { 
		$sqlSubCat = ' AND C.ParentID = 0';
		$catType = 'MainCat';
		$mainID  = 0;
	}
	
	$sql = "SELECT C.CatID,C.CatTitle,COUNT(FID) AS NoOfFiles
			FROM cdc_sysCategory C
			LEFT JOIN cdc_Files F ON F.CatID = C.CatID 
			WHERE 
				(C.SiteArea = '$siteArea' AND 
				C.Active = 1)
				$sqlSubCat				
			GROUP BY C.CatID
			ORDER BY C.CatTitle";
	$result = mysql_db_query($dbname,$sql);	
	
	if (mysql_num_rows($result)>0) {	
		$returnString = '';
		$stringJSON   = '({"SiteArea":"'.$siteArea.'","CatType":"'.$catType.'","MainID":"'.$mainID.'","Categories":[';
		
		while ($value = mysql_fetch_assoc($result)) {
			$value['NoOfFiles'] = getFileCount($value['CatID']);
		
			$stringJSON .= Zend_Json::encode($value).',';
		}
		$stringJSON = substr($stringJSON,0,-1);
		$stringJSON .= ']})';
		$returnString = $stringJSON;
		return $returnString;	
	} else {
		return 0;	
	}
}

function deleteFile($fileID) {
	global $dbname, $db, $db_selected;
	
	/*
	 * Get file path for deletion
	 */
	$sql = "SELECT 
				F.FID,F.CatID,F.FileTitle,F.FileDesc,F.FileName,F.FileSize,F.FileType,F.DateUpdate,
				C.DirectoryName
			FROM cdc_Files F
			LEFT JOIN cdc_sysCategory C ON F.CatID = C.CatID
			WHERE 
				F.FID = '$fileID'";
	$result = mysql_db_query($dbname,$sql);
	$row = mysql_fetch_assoc($result);
		
	if (empty($row['DirectoryName'])) {
		$filePath = PUBLIC_FILES_PATH.'resource_files/'.$row['FileName'];	
	} else {
		$filePath = PUBLIC_FILES_PATH.'resource_files/'.$row['DirectoryName'].'/'.$row['FileName'];
	}
	
	/*
	 * Delete file database entry
	 */
	$sql = "DELETE from cdc_Files WHERE FID = '$fileID'";
	$result = mysql_db_query($dbname,$sql);

	/*
	 * Remove file form file system
	 */
	@unlink($filePath);
}

function getFileCount($catID) {
	global $dbname, $db, $db_selected;
	
	/*
	 * Is $catID a main-cat or sub-cat?
	 */
	$sql = "SELECT ParentID from cdc_sysCategory WHERE CatID = '$catID'";
	$result = mysql_db_query($dbname,$sql);
	$row = mysql_fetch_assoc($result);
	if ($row['ParentID']>0) {
		/*
		 * $catID is a sub-category, get number of files for this sub-category
		 */
		$sql2 = "SELECT COUNT(FID) AS NoOfFiles from cdc_Files 
					WHERE CatID = '$catID' AND
					Deleted = 0";
		$result2 = mysql_db_query($dbname,$sql2);
		$row2 = mysql_fetch_assoc($result2);
		$noOfFiles = $row2['NoOfFiles'];
	} else {
		/*
		 * $catID is a main category, check for any sub-categories
		 */
		$sql2 = "SELECT CatID from cdc_sysCategory WHERE ParentID = '$catID'";
		$result2 = mysql_db_query($dbname,$sql2);
		if (mysql_num_rows($result2)>0) {
			$noOfFiles = 0;
			while ($value = mysql_fetch_assoc($result2)) {
				$newCatID = $value['CatID'];
				
				$sql3 = "SELECT COUNT(FID) AS NoOfFiles from cdc_Files 
							WHERE CatID = '$newCatID' AND
							Deleted = 0";
				$result3 = mysql_db_query($dbname,$sql3);
				$row3 = mysql_fetch_assoc($result3);
				$noOfFiles = $noOfFiles+$row3['NoOfFiles'];				
			}
		} else {
			/*
			 * There are no sub-categories, just get files for this category
			 */
			$sql3 = "SELECT COUNT(FID) AS NoOfFiles from cdc_Files 
						WHERE CatID = '$catID'  AND
						Deleted = 0";
			$result3 = mysql_db_query($dbname,$sql3);
			$row3 = mysql_fetch_assoc($result3);
			$noOfFiles = $row3['NoOfFiles'];
		}
	}
	return $noOfFiles;
}

function saveResource($phpJSON) {
	global $dbname, $db, $db_selected;
	// Decode JSON
	$phpJSON = urldecode(stripslashes($phpJSON));
	$phpArray = Zend_Json::decode($phpJSON);
	
	$action       = $phpArray['action'];
	$resourceID   = $phpArray['resourceID'];
	$counties     = $phpArray['counties'];
	$rType        = $phpArray['rType'];
	$rName        = fieldToDB($phpArray['rName']);
	$rDesc        = fieldToDB($phpArray['rDescription']);
	$rURL         = fieldToDB($phpArray['rURL']);
	$rContact     = fieldToDB($phpArray['rContact']);
	$rServiceArea = fieldToDB($phpArray['rServiceArea']);  
	$rAddress     = fieldToDB($phpArray['rAddress']); 
	$rCity        = fieldToDB($phpArray['rCity']);
	$rState       = fieldToDB($phpArray['rState']);
	$rZip         = fieldToDB($phpArray['rZip']);
	$rPhone       = fieldToDB($phpArray['rPhone']);
	$rFax         = fieldToDB($phpArray['rFax']);
	$rEmail       = fieldToDB($phpArray['rEmail']);
	$rNotes       = fieldToDB($phpArray['rNotes']);
	
	if ($action == 'add') {
		$sql = "INSERT into cdc_ResourceProviders (
				CatID,
				ProviderName,
				ProviderDescription,
				ProviderServiceArea,
				ProviderNotes,
				ProviderWebURL,
				ProviderContactPerson,
				ProviderPhone,
				ProviderFax,
				ProviderEmail,
				ProviderAddress,
				ProviderCity,
				ProviderState,
				ProviderZip,
				ProviderCounties,
				DateUpdate,
				UseridEntry
				) values (
				'$rType',
				'$rName',
				'$rDesc',
				'$rServiceArea',
				'$rNotes',
				'$rURL',
				'$rContact',
				'$rPhone',
				'$rFax',
				'$rEmail',
				'$rAddress',
				'$rCity',
				'$rState',
				'$rZip',
				'$counties',
				'".date('Y-m-d G:i:s')."',
				'".$_SESSION['userid']."'
				)";
		$result = mysql_db_query($dbname,$sql);
	
		// Retrieve entry ID
		$resourceID = mysql_insert_id();
	} elseif ($action == 'edit') {
		$sql = "UPDATE cdc_ResourceProviders SET
				CatID                 = '$rType',
				ProviderName          = '$rName',
				ProviderDescription   = '$rDesc',
				ProviderServiceArea   = '$rServiceArea',
				ProviderNotes         = '$rNotes',
				ProviderWebURL        = '$rURL',
				ProviderContactPerson = '$rContact',
				ProviderPhone         = '$rPhone',
				ProviderFax           = '$rFax',
				ProviderEmail         = '$rEmail',
				ProviderAddress       = '$rAddress',
				ProviderCity          = '$rCity',
				ProviderState         = '$rState',
				ProviderZip           = '$rZip',
				ProviderCounties      = '$counties',
				DateUpdate            = '".date('Y-m-d G:i:s')."',
				UseridLastUpdate      = '".$_SESSION['userid']."'
				WHERE
				ProviderID = '$resourceID'";
		$result = mysql_db_query($dbname,$sql);
	}
	
	return $resourceID.'|'.$rType;
}

function getResources($catID) {
	global $dbname, $db, $db_selected;
	
	$sql = "SELECT * from cdc_ResourceProviders 
			WHERE CatID = '$catID'
			ORDER BY ProviderName";
	$result = mysql_db_query($dbname,$sql);
	if (mysql_num_rows($result)>0) {
		$stringJSON   = '({"Resources":[';
		while ($value = mysql_fetch_assoc($result)) {
			$dateEntered = new Zend_Date($value['DateUpdate'], Zend_Date::ISO_8601, 'en_US');
			$value['DateEntered'] = $dateEntered->toString('MMM d, Y');
			$value['Counties']    = str_replace('|',',',$value['ProviderCounties']);
			$value['Counties']    = substr($value['Counties'],0,-1);
			
			$stringJSON .= Zend_Json::encode($value).',';		
		}
		$stringJSON = substr($stringJSON,0,-1);
		$stringJSON .= ']})';		
	} else {
		$stringJSON = 0;
	}	
	return $stringJSON;
}
?>